import json
from openai import OpenAI
import mysql.connector
from mysql.connector import Error
import time

# 初始化
client = OpenAI(api_key="sk-427136e7d3bd47099bf14ae993d25b91", base_url="https://api.deepseek.com")
DB_CONFIG = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "",  # 替换为你的密码
    "database": "stock_db",
    "charset": "utf8mb4"
}


def analyze_company_with_ai(stock_code, company_name):
    """
    使用DeepSeek分析上市公司数据 - 增强版
    """
    system_prompt = """
    你是一个专业的金融投资分析师。对上市公司进行深度分析。
    请基于申万行业分类体系和最新市场数据，以JSON格式返回分析结果。
    对于行业分类，申万体系主要包含三级分类，更细分的级别需要合理推断。
    对于财务数据和股东信息，请基于行业常识和公司特点进行合理估算。
    """

    user_prompt = f"""
    请对以下上市公司进行深度分析：
    股票代码：{stock_code}
    上市公司名称：{company_name}
    请以JSON格式返回分析结果，包含以下字段：
    - stock_code: 股票代码
    - company_name: 上市公司名称  
    - secondary_industry: 二级行业（基于申万分类）
    - tertiary_industry: 三级行业（基于申万分类）
    - quaternary_industry: 四级行业（合理推断）
    - quinary_industry: 五级行业（合理推断）
    - senary_industry: 六级行业（合理推断）
    - septenary_industry: 七级行业（合理推断）
    - octonary_industry: 八级行业（合理推断）
    - nonary_industry: 九级行业（合理推断）
    - denary_industry: 十级行业（合理推断）
    - company_concepts: 公司包含的概念（数组格式）
    - industry_position_description: 细分行业中的地位详细描述
    - industry_position_key_points: 细分行业中的地位关键点（数组格式）
    - industry_position_best_points: 细分行业中地位最佳关键点（数组格式）
    - revenue_description: 公司营收主营详细描述以及占比
    - revenue_breakdown: 营业收入占比成分详细描述（JSON格式）
    - competitors: 细分行业中竞争对手（数组格式）
    - advantages_over_competitors: 细分行业中优于竞争对手的亮点（数组格式）
    - financial_highlights: 截止2025年10月31日财报亮点描述
    - top10_float_shareholders: 截止2025年10月31日10大流通股东以及占比（JSON格式）
    - related_listed_companies: 关联的上市公司（数组格式）
    - growth_description: 公司成长性详细描述
    - growth_key_points: 公司成长性关键点（数组格式）
    - quantitative_highlights: 公司亮点定量分析（JSON格式）
    - irreplaceability_description: 不可替代性、稀缺性描述
    - franchise_rights: 特许经营权
    - patents: 专利情况
    - post_oct2025_pledge_info: 2025年10月后解禁质押信息
    - total_capital: 总股本
    - float_capital: 流通股本
    重要说明：
    1. 行业分类基于申万体系，参考当前A股市场结构
    2. 财务数据参考2025年三季报趋势
    3. 股本数据遵循标准定义
    4. 护城河分析参考无形资产、专利、品牌等概念
    5. 质押信息格式参考上市公司公告
    请确保返回合法的JSON格式，所有字段都必须包含，如无数据可设为空字符串或空数组。
    """
    messages = [
        {"role": "system", "content": system_prompt},
        {"role": "user", "content": user_prompt}
    ]

    try:
        response = client.chat.completions.create(
            model="deepseek-chat",
            messages=messages,
            response_format={'type': 'json_object'},
            timeout=60
        )
        result = json.loads(response.choices[0].message.content)
        # 确保股票代码和名称正确
        result['stock_code'] = stock_code
        result['company_name'] = company_name
        return result
    except Exception as e:
        print(f"AI分析失败 {stock_code}: {e}")
        return None


def create_analysis_table():
    """
    创建分析结果表
    """
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()

        create_table_sql = """
        CREATE TABLE IF NOT EXISTS stock_analysis_results (
            id INT AUTO_INCREMENT PRIMARY KEY,
            stock_code VARCHAR(20) NOT NULL UNIQUE,
            company_name VARCHAR(255),
            secondary_industry VARCHAR(255),
            tertiary_industry VARCHAR(255),
            quaternary_industry VARCHAR(255),
            quinary_industry VARCHAR(255),
            senary_industry VARCHAR(255),
            septenary_industry VARCHAR(255),
            octonary_industry VARCHAR(255),
            nonary_industry VARCHAR(255),
            denary_industry VARCHAR(255),
            company_concepts JSON,
            industry_position_description TEXT,
            industry_position_key_points JSON,
            industry_position_best_points JSON,
            revenue_description TEXT,
            revenue_breakdown JSON,
            competitors JSON,
            advantages_over_competitors JSON,
            financial_highlights TEXT,
            top10_float_shareholders JSON,
            related_listed_companies JSON,
            growth_description TEXT,
            growth_key_points JSON,
            quantitative_highlights JSON,
            irreplaceability_description TEXT,
            franchise_rights TEXT,
            patents TEXT,
            post_oct2025_pledge_info TEXT,
            total_capital VARCHAR(100),
            float_capital VARCHAR(100),
            analysis_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_stock_code (stock_code),
            INDEX idx_industry (secondary_industry)
        )
        """
        cursor.execute(create_table_sql)
        conn.commit()
        print("分析结果表创建/验证成功")

    except Error as e:
        print(f"创建表失败: {e}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()


def enhanced_batch_analyze(limit=None):
    """
    增强版批量分析函数
    """
    try:
        # 数据库连接
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor(dictionary=True)

        # 获取股票列表
        if limit:
            cursor.execute("SELECT code, name FROM a_stock_basic_info LIMIT %s", (limit,))
        else:
            cursor.execute("SELECT code, name FROM a_stock_basic_info")
        stocks = cursor.fetchall()

        print(f"共获取到 {len(stocks)} 只股票，开始批量分析...")

        # 创建结果表
        create_analysis_table()

        # 重新获取数据库连接（避免超时）
        if conn.is_connected():
            cursor.close()
            conn.close()

        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()

        # 批量分析
        success_count = 0
        for i, stock in enumerate(stocks):
            print(f"分析进度: {i + 1}/{len(stocks)} - {stock['code']} {stock['name']}")

            result = analyze_company_with_ai(stock['code'], stock['name'])

            if result:
                # 插入数据库
                insert_sql = """
                INSERT INTO stock_analysis_results (
                    stock_code, company_name, secondary_industry, tertiary_industry,
                    quaternary_industry, quinary_industry, senary_industry, septenary_industry,
                    octonary_industry, nonary_industry, denary_industry, company_concepts,
                    industry_position_description, industry_position_key_points, industry_position_best_points,
                    revenue_description, revenue_breakdown, competitors, advantages_over_competitors,
                    financial_highlights, top10_float_shareholders, related_listed_companies,
                    growth_description, growth_key_points, quantitative_highlights,
                    irreplaceability_description, franchise_rights, patents, post_oct2025_pledge_info,
                    total_capital, float_capital
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                    company_name=VALUES(company_name),
                    secondary_industry=VALUES(secondary_industry),
                    tertiary_industry=VALUES(tertiary_industry),
                    analysis_date=CURRENT_TIMESTAMP
                """

                try:
                    cursor.execute(insert_sql, (
                        result.get('stock_code', ''),
                        result.get('company_name', ''),
                        result.get('secondary_industry', ''),
                        result.get('tertiary_industry', ''),
                        result.get('quaternary_industry', ''),
                        result.get('quinary_industry', ''),
                        result.get('senary_industry', ''),
                        result.get('septenary_industry', ''),
                        result.get('octonary_industry', ''),
                        result.get('nonary_industry', ''),
                        result.get('denary_industry', ''),
                        json.dumps(result.get('company_concepts', []), ensure_ascii=False),
                        result.get('industry_position_description', ''),
                        json.dumps(result.get('industry_position_key_points', []), ensure_ascii=False),
                        json.dumps(result.get('industry_position_best_points', []), ensure_ascii=False),
                        result.get('revenue_description', ''),
                        json.dumps(result.get('revenue_breakdown', {}), ensure_ascii=False),
                        json.dumps(result.get('competitors', []), ensure_ascii=False),
                        json.dumps(result.get('advantages_over_competitors', []), ensure_ascii=False),
                        result.get('financial_highlights', ''),
                        json.dumps(result.get('top10_float_shareholders', {}), ensure_ascii=False),
                        json.dumps(result.get('related_listed_companies', []), ensure_ascii=False),
                        result.get('growth_description', ''),
                        json.dumps(result.get('growth_key_points', []), ensure_ascii=False),
                        json.dumps(result.get('quantitative_highlights', {}), ensure_ascii=False),
                        result.get('irreplaceability_description', ''),
                        result.get('franchise_rights', ''),
                        result.get('patents', ''),
                        result.get('post_oct2025_pledge_info', ''),
                        result.get('total_capital', ''),
                        result.get('float_capital', '')
                    ))
                    conn.commit()
                    success_count += 1
                    print(f"  ✅ 成功保存 {stock['code']}")

                except Error as e:
                    print(f"  ❌ 数据库插入失败 {stock['code']}: {e}")
                    conn.rollback()
            else:
                print(f"  ❌ 分析失败 {stock['code']}")

            # 添加延迟避免API限制
            time.sleep(2)

            # 每10只股票输出一次进度
            if (i + 1) % 10 == 0:
                print(f"=== 已完成 {i + 1} 只股票分析，成功 {success_count} 只 ===")

        print(f"批量分析完成！成功分析 {success_count}/{len(stocks)} 只股票")

    except Error as e:
        print(f"数据库连接失败: {e}")
    except Exception as e:
        print(f"程序执行出错: {e}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()


def test_single_analysis():
    """
    测试单个股票分析
    """
    test_code = "000001"
    test_name = "平安银行"
    print(f"测试分析股票: {test_code} {test_name}")

    result = analyze_company_with_ai(test_code, test_name)
    if result:
        print("分析结果:")
        print(json.dumps(result, ensure_ascii=False, indent=2))

        # 测试插入数据库
        create_analysis_table()
        try:
            conn = mysql.connector.connect(**DB_CONFIG)
            cursor = conn.cursor()

            insert_sql = """
            INSERT INTO stock_analysis_results (
                stock_code, company_name, secondary_industry, tertiary_industry,
                quaternary_industry, quinary_industry, senary_industry, septenary_industry,
                octonary_industry, nonary_industry, denary_industry, company_concepts,
                industry_position_description, industry_position_key_points, industry_position_best_points,
                revenue_description, revenue_breakdown, competitors, advantages_over_competitors,
                financial_highlights, top10_float_shareholders, related_listed_companies,
                growth_description, growth_key_points, quantitative_highlights,
                irreplaceability_description, franchise_rights, patents, post_oct2025_pledge_info,
                total_capital, float_capital
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """

            cursor.execute(insert_sql, (
                result.get('stock_code', ''),
                result.get('company_name', ''),
                result.get('secondary_industry', ''),
                result.get('tertiary_industry', ''),
                result.get('quaternary_industry', ''),
                result.get('quinary_industry', ''),
                result.get('senary_industry', ''),
                result.get('septenary_industry', ''),
                result.get('octonary_industry', ''),
                result.get('nonary_industry', ''),
                result.get('denary_industry', ''),
                json.dumps(result.get('company_concepts', []), ensure_ascii=False),
                result.get('industry_position_description', ''),
                json.dumps(result.get('industry_position_key_points', []), ensure_ascii=False),
                json.dumps(result.get('industry_position_best_points', []), ensure_ascii=False),
                result.get('revenue_description', ''),
                json.dumps(result.get('revenue_breakdown', {}), ensure_ascii=False),
                json.dumps(result.get('competitors', []), ensure_ascii=False),
                json.dumps(result.get('advantages_over_competitors', []), ensure_ascii=False),
                result.get('financial_highlights', ''),
                json.dumps(result.get('top10_float_shareholders', {}), ensure_ascii=False),
                json.dumps(result.get('related_listed_companies', []), ensure_ascii=False),
                result.get('growth_description', ''),
                json.dumps(result.get('growth_key_points', []), ensure_ascii=False),
                json.dumps(result.get('quantitative_highlights', {}), ensure_ascii=False),
                result.get('irreplaceability_description', ''),
                result.get('franchise_rights', ''),
                result.get('patents', ''),
                result.get('post_oct2025_pledge_info', ''),
                result.get('total_capital', ''),
                result.get('float_capital', '')
            ))
            conn.commit()
            print("测试数据插入成功！")

        except Error as e:
            print(f"测试数据插入失败: {e}")
        finally:
            if 'conn' in locals() and conn.is_connected():
                cursor.close()
                conn.close()
    else:
        print("分析失败")


def check_analysis_results():
    """
    检查分析结果
    """
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor(dictionary=True)

        cursor.execute("SELECT COUNT(*) as count FROM stock_analysis_results")
        total = cursor.fetchone()['count']

        cursor.execute("SELECT stock_code, company_name, secondary_industry FROM stock_analysis_results LIMIT 5")
        samples = cursor.fetchall()

        print(f"当前分析结果表中共有 {total} 条记录")
        print("前5条记录样例:")
        for sample in samples:
            print(f"  {sample['stock_code']} - {sample['company_name']} - {sample['secondary_industry']}")

    except Error as e:
        print(f"检查结果失败: {e}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()


if __name__ == '__main__':
     enhanced_batch_analyze(20)
